package com.bss.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import javax.servlet.http.HttpSession;

import com.bss.beans.DailyRevenue;
import com.bss.beans.Paymentsmade;
import com.bss.utilities.DateUtilities;

public class PaymentDao {
		
	public static String findLastPaymentDate(int transactionId) throws Exception
	{
		String date="";
		
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		
		String insertString="select max(paymentupto) from paymentsmade where transactionId=?";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Ps.setInt(1,transactionId);
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				if(Rs.getTimestamp(1)!=null)
				{
					date=Rs.getTimestamp(1).toString();
				}
				else
				{
					date="";
					
				}
			}

		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		
		return date;
	}
	
	public static boolean addPayment(Paymentsmade payment) throws Exception
	{
		Connection conn;
		PreparedStatement Ps;
		String insertString="insert into paymentsmade values(?,?,?,?,?,?,?,?,?)";
		conn=ConnectionDao.getConnection1();
		
		try{

			Ps=conn.prepareStatement(insertString);
			Ps.setInt(1, payment.getPaymentId());
			Ps.setInt(2, payment.getTransactionId());
			Ps.setTimestamp(3,DateUtilities.convertToTimestamp(payment.getPaymentDate()));
			Ps.setTimestamp(4,DateUtilities.convertToTimestamp(payment.getPaymentUpto()));
			Ps.setTimestamp(5,DateUtilities.convertToTimestamp(payment.getPaymentFrom()));
			Ps.setString(6, payment.getRooms());
			Ps.setString(7, payment.getDoms());
			Ps.setInt(8	,payment.getAmount());
			Ps.setInt(9, payment.getNoOfDays());

			Ps.executeUpdate();
		
	}
	finally
	{
		ConnectionDao.closeConnection(conn);
	
	}
	
		return true;
	}
	
	public static int FindMaxPaymentId() throws Exception
	{
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		int patientId=0;
		
		String insertString="select max(paymentId) from paymentsmade";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				patientId=Rs.getInt(1);
			}
				
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return patientId;
	}
	
	public static ArrayList<Paymentsmade> paymentsMade(ArrayList<Integer> transactionIds) throws Exception
	{
		ArrayList<Paymentsmade> payments=new ArrayList<Paymentsmade>();
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		
		String insertString="select * from paymentsmade ";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				if(transactionIds.contains(Rs.getInt(2))==true)
				{
					Paymentsmade payment=new Paymentsmade();
					payment.setPaymentId(Rs.getInt(1));
					payment.setTransactionId(Rs.getInt(2));
					payment.setPaymentDate(Rs.getTimestamp(3).toString());
					payment.setPaymentUpto(Rs.getTimestamp(4).toString());
					payment.setPaymentFrom(Rs.getTimestamp(5).toString());
					payment.setRooms(Rs.getString(6));
					payment.setDoms(Rs.getString(7));
					payment.setAmount(Rs.getInt(8));
					payment.setNoOfDays(Rs.getInt(9));
					
					payments.add(payment);
					
				}
			}

		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		
		
		
		return payments;
	}
	
	public static String latestDailyAccountReportDate() throws Exception
	{
		
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		String date="";
		
		String insertString="select max(dateOfReport) from dailyaccountreporting";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				date=Rs.getTimestamp(1).toString();
			}
				
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return date;
	}
	
	public static String latestDailyRevenueReportDate() throws Exception
	{
		
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		String date="";
		
		String insertString="select max(dateGenerated) from dailyrevenue";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Rs=Ps.executeQuery();
			
			if(Rs !=null && Rs.next())
			{
				Timestamp tt = Rs.getTimestamp(1);
				if(tt!=null)
					date = tt.toString();
				else
					date = "";
			}		
			else {
				date = "";
			}
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return date;
	}
	
	public static int oldestReceiptNo(String date1, String date2) throws Exception
	{
		int paymentId=0;
		ResultSet Rs;
		PreparedStatement Ps;
		Connection conn;
		String insertString="select min(paymentId) from paymentsmade where date >= ? and date < ?";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Ps.setTimestamp(1, DateUtilities.convertToTimestamp(date1));
			Ps.setTimestamp(2, DateUtilities.convertToTimestamp(date2));
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				paymentId=Rs.getInt(1);
			}
				
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return paymentId;
	}
	
	
	public static int latestReceiptNo(String date1, String date2) throws Exception
	{
		int paymentId=0;
		ResultSet Rs;
		PreparedStatement Ps;
		Connection conn;
		String insertString="select max(paymentId) from paymentsmade where date >= ? and date < ?";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Ps.setTimestamp(1, DateUtilities.convertToTimestamp(date1));
			Ps.setTimestamp(2, DateUtilities.convertToTimestamp(date2));
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				paymentId=Rs.getInt(1);
			}
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return paymentId;
	}
	
	public static int totalCollectionAmount(int startTrId,int stopTrId) throws Exception
	{
		int totalAmount=0;
		ResultSet Rs;
		PreparedStatement Ps;
		Connection conn;
		String insertString="select sum(amount) from paymentsmade where paymentId >= ?  and paymentId <= ?";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Ps.setInt(1,startTrId );
			Ps.setInt(2,stopTrId );
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				totalAmount=Rs.getInt(1);
			}
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return totalAmount;
	}
	
	public static int dailyAccountClosingBalRoom(String date) throws Exception
	{
		int openingBal=0;
		ResultSet Rs;
		PreparedStatement Ps;
		Connection conn;
		String insertString="select closingBalRoom from dailyaccountreporting where dateOfReport < ? ";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Ps.setTimestamp(1, DateUtilities.convertToTimestamp(date));
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				openingBal=Rs.getInt(1);
			}
				
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return openingBal;
	}
	
	
	public static int dailyAccountClosingBalUtensil(String date) throws Exception
	{
		int openingBal=0;
		ResultSet Rs;
		PreparedStatement Ps;
		Connection conn;
		String insertString="select closingBalUtensil from dailyaccountreporting where dateOfReport < ? ";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Ps.setTimestamp(1, DateUtilities.convertToTimestamp(date));
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				openingBal=Rs.getInt(1);
			}
				
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return openingBal;
	}
	
	
	public static ArrayList<Paymentsmade> findPaymentsMade(int transactionId) throws Exception
	{
		ArrayList<Paymentsmade> payments = new ArrayList<Paymentsmade>();
		
		ResultSet Rs;
		PreparedStatement Ps;
		Connection conn;
		String insertString="select paymentupto, date, paymentId from paymentsmade where transactionId = ?";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Ps.setInt(1, transactionId);
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				Paymentsmade payment = new Paymentsmade();
				payment.setPaymentUpto(Rs.getTimestamp(1).toString());
				payment.setPaymentDate(Rs.getTimestamp(2).toString());
				payment.setPaymentId(Rs.getInt(3));
				payments.add(payment);
			}
				
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return payments;
		
	}
	
	public boolean updateDailyAccountRecord(HttpSession session) throws Exception
	{
		Connection conn;
		PreparedStatement Ps;
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
		
		String strSelect = "insert into dailyaccountreporting values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		Ps=conn.prepareStatement(strSelect);
		
		Ps.setInt(1, FindMaxDailyAccountReportId()+1);
		Ps.setTimestamp(2, DateUtilities.convertToTimestamp(session.getAttribute("date").toString()));
		Ps.setString(3, null);
		Ps.setString(4, null);
		Ps.setString(5, null);
		Ps.setString(6, null);
		Ps.setString(7, null);
		Ps.setString(8, null);
		Ps.setString(9, null);
		Ps.setInt(10, 0);
		Ps.setString(11, null);
		Ps.setInt(12, 0);
		Ps.setString(13, null);
		Ps.setInt(14, 0);
		Ps.setString(15, null);
		Ps.setInt(16, 0);
		Ps.setString(17, null);
		Ps.setInt(18, 0);
		Ps.setString(19, null);
		Ps.setInt(20, 0);
		Ps.setString(21, null);
		Ps.setInt(22, 0);
		Ps.setString(23, null);
		Ps.setInt(24, 0);
		Ps.setString(25, null);
		Ps.setInt(26, 0);
		Ps.setString(27, null);
		Ps.setInt(28, 0);
		Ps.setString(29, null);
		Ps.setInt(30, 0);
		Ps.setString(31, null);
		Ps.setInt(32, 0);
		Ps.setString(33, null);
		Ps.setInt(34, 0);
		Ps.setString(35, null);
		Ps.setInt(36, 0);
		Ps.setInt(37, Integer.parseInt(session.getAttribute("openingBalanceRoom").toString()));
		Ps.setInt(38, Integer.parseInt(session.getAttribute("closingBalanceRoom").toString()));
		Ps.setInt(39, Integer.parseInt(session.getAttribute("openingBalanceUtensil").toString()));
		Ps.setInt(40, Integer.parseInt(session.getAttribute("closingBalanceUtensil").toString()));
		Ps.setInt(41, 0);
		Ps.setString(42, null);
		Ps.setString(43, null);
		Ps.setString(44, null);
		Ps.executeUpdate();
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		
		return true;
	}
	
	
	public boolean update(DailyRevenue dailyRevenue) throws Exception {

		Connection conn = ConnectionDao.getConnection1();

		String strSelect = "Insert into DailyRevenue values(0,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		PreparedStatement ps = conn.prepareStatement(strSelect);

		ps.setInt(1, dailyRevenue.getRasoiMorning());
		ps.setInt(2, dailyRevenue.getRasoiEvening());
		ps.setInt(3, dailyRevenue.getwReceiptStart());
		ps.setInt(4, dailyRevenue.getwReceiptEnd());
		ps.setInt(5, dailyRevenue.getReceiptTotal());
		ps.setInt(6, dailyRevenue.getBusMorning());
		ps.setInt(7, dailyRevenue.getBusAfternoon());
		ps.setInt(8, dailyRevenue.getBusEvening());
		ps.setInt(9, dailyRevenue.getBusMini());
		ps.setString(10, dailyRevenue.getAmbulanceDetail1());
		ps.setInt(11, dailyRevenue.getAmbulanceRevenue1());
		ps.setString(12, dailyRevenue.getAmbulanceDetail2());
		ps.setInt(13, dailyRevenue.getAmbulanceRevenue2());
		ps.setString(14, dailyRevenue.getOthersDetail1());
		ps.setInt(15, dailyRevenue.getOthersRevenue1());
		ps.setString(16, dailyRevenue.getOthersDetail2());
		ps.setInt(17, dailyRevenue.getOthersRevenue2());
		ps.setInt(18, dailyRevenue.getTotal());
		ps.setTimestamp(19, DateUtilities.convertToTimestamp(dailyRevenue
				.getDateGenerated()));

		ps.executeUpdate();

		return true;

	}

	
	public static int FindMaxDailyAccountReportId() throws Exception
	{
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		int Id=0;
		
		String insertString="select max(reportId) from dailyaccountreporting";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				Id=Rs.getInt(1);
			}
				
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return Id;
	}
	

	public static boolean loadDailyAccountRecord(HttpSession session, int reportId) throws Exception
	{
		ResultSet Rs;
		PreparedStatement Ps;
		Connection conn;
		String insertString="select * from dailyaccountreporting where reportId = ?";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Ps.setInt(1, reportId);
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				session.setAttribute("date", DateUtilities.dbDateToCalDate(Rs.getTimestamp(2).toString()));
				session.setAttribute("col1a", Rs.getString(3));
				session.setAttribute("col2a", Rs.getString(4));
				session.setAttribute("col5a", Rs.getString(5));
				session.setAttribute("col6a", Rs.getString(6));
				session.setAttribute("col7a", Rs.getString(7));
				session.setAttribute("col8a", Rs.getString(8));
				session.setAttribute("col9a", Rs.getString(9));
				session.setAttribute("col9b", Rs.getInt(10));
				session.setAttribute("col10a", Rs.getString(11));
				session.setAttribute("col10b", Rs.getInt(12));
				session.setAttribute("exp1a", Rs.getString(13));
				session.setAttribute("exp1b", Rs.getInt(14));
				session.setAttribute("exp2a", Rs.getString(15));
				session.setAttribute("exp2b", Rs.getInt(16));
				session.setAttribute("exp3a", Rs.getString(17));
				session.setAttribute("exp3b", Rs.getInt(18));
				session.setAttribute("exp4a", Rs.getString(19));
				session.setAttribute("exp4b", Rs.getInt(20));
				session.setAttribute("exp5a", Rs.getString(21));
				session.setAttribute("exp5b", Rs.getInt(22));
				session.setAttribute("exp6a", Rs.getString(23));
				session.setAttribute("exp6b", Rs.getInt(24));
				session.setAttribute("exp7a", Rs.getString(25));
				session.setAttribute("exp7b", Rs.getInt(26));
				session.setAttribute("exp8a", Rs.getString(27));
				session.setAttribute("exp8b", Rs.getInt(28));
				session.setAttribute("exp9a", Rs.getString(29));
				session.setAttribute("exp9b", Rs.getInt(30));
				session.setAttribute("exp10a", Rs.getString(31));
				session.setAttribute("exp10b", Rs.getInt(32));
				session.setAttribute("exp11a", Rs.getString(33));
				session.setAttribute("exp11b", Rs.getInt(34));
				session.setAttribute("exp12a", Rs.getString(35));
				session.setAttribute("exp12b", Rs.getInt(36));
				session.setAttribute("openingBalanceRoom", Rs.getInt(37));
				session.setAttribute("closingBalanceRoom", Rs.getInt(38));
				session.setAttribute("openingBalanceUtensil", Rs.getInt(39));
				session.setAttribute("closingBalanceUtensil", Rs.getInt(40));
				session.setAttribute("redreceipt", Rs.getInt(41));
				session.setAttribute("redreceiptno", Rs.getString(42));
			}
				
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		
		return true;
	}
	
}
